﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;

namespace Fotobanka
{
    /// <summary>
    /// Osluzna trida tabulky User_Galleries
    /// </summary>
    public class UserGalleriesManager
    {
        SqlConnection connection;
        SqlCommand command;
        SqlDataReader reader;

        /// <summary>
        /// Vraci seznam uzivatelu (id, email) kteri nejsou pro danou galerii (gallery_id) prirazeni
        /// </summary>
        /// <param name="gallery_id">id galerie</param>
        /// <returns>seznam uzivatelu</returns>
        public List<UserObject> SelectUsersBezPrirazeneGalerie(int gallery_id)
        {
            List<UserObject> l = new List<UserObject>();
            using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
            {
                connection.Open();
                command = new SqlCommand("SELECT email, id FROM Users WHERE (id NOT IN (SELECT user_id FROM User_Galleries WHERE (gallery_id = @id)))", connection);
                command.Parameters.Add(new SqlParameter("@id", gallery_id));

                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    UserObject new_user = new UserObject();
                    new_user.Id = Convert.ToInt32(reader["id"]);
                    new_user.Email = reader["email"].ToString();
                    l.Add(new_user);
                }
            }
            return l;
        }
        
        /// <summary>
        /// Vybere uzivatele, kteri jsou prirazeni dane skupine
        /// </summary>
        /// <param name="gallery_id">id galerie</param>
        /// <returns>seznam uzivatelu</returns>
        public List<UserObject> SelectUsersSPrirazenouGalerii(int gallery_id)
        {
            List<UserObject> l = new List<UserObject>();
            using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
            {
                connection.Open();
                command = new SqlCommand("SELECT User_Galleries.id, Users.email, Users.first_name, Users.last_name FROM Users INNER JOIN User_Galleries ON Users.id = User_Galleries.user_id WHERE (User_Galleries.gallery_id = @id)", connection);
                command.Parameters.Add(new SqlParameter("@id", gallery_id));

                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    UserObject new_user = new UserObject();
                    new_user.Id = Convert.ToInt32(reader["id"]);
                    new_user.Email = reader["email"].ToString();
                    new_user.FirstName = reader["first_name"].ToString();
                    new_user.LastName = reader["last_name"].ToString();
                    l.Add(new_user);
                }
            }
            return l;
        }

        /// <summary>
        /// Prida uzivatele ke galerii
        /// </summary>
        /// <param name="user_id">id uzivatele</param>
        /// <param name="gallery_id">id galerie</param>
        public void insertUserIntoGallery(int user_id, int gallery_id)
        {
            using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
            {
                connection.Open();
                command = new SqlCommand("INSERT INTO User_Galleries  (gallery_id, user_id, created_on) VALUES (@gallery_id, @user_id, @created_on);", connection);
                command.Parameters.Add(new SqlParameter("@gallery_id", gallery_id));
                command.Parameters.Add(new SqlParameter("@user_id", user_id));
                command.Parameters.Add(new SqlParameter("@created_on", DateTime.Now));

                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Pro vybranou galerie smaze uzivatele (jeho pristup)
        /// </summary>
        /// <param name="id">id uzivele ve skupine</param>
        public void deleteFromDB(int id)
        {
            using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
            {
                connection.Open();

                command = new SqlCommand("DELETE FROM User_Galleries WHERE (id = @u_id);", connection);
                command.Parameters.Add(new SqlParameter("@u_id", id));
                //command.Parameters.Add(new SqlParameter("@g_id", gallery_id));

                command.ExecuteNonQuery();
            }
        }
    }
}
